package com.score.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.score.mapper.ICheckDormMapper;
import com.score.mapper.IDormMapper;
import com.score.mapper.IFiveoneMapper;
import com.score.pojo.ResponseEntry;
import com.score.pojo.TCheckDorm;
import com.score.pojo.TDorm;
import com.score.pojo.TFiveone;
import com.score.util.ExcelUtils;
import org.apache.poi.hssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.access.annotation.Secured;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

@Service
@Transactional
public class UpDownLoadService {

    @Autowired
    private IDormMapper dormMapper;

    public ResponseEntry UploadExcel(MultipartFile file) throws ParseException {
        if (file.isEmpty()) {
            try {
                throw new Exception("文件不存在！");
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        InputStream in = null;
        try {
            in = file.getInputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }
        List<List<Object>> listob = null;
        try {
            listob = new ExcelUtils().getBankListByExcel(in, file.getOriginalFilename());
            //          System.out.println("listob触发了"+listob);
        } catch (Exception e) {
            e.printStackTrace();
        }
        //该处可调用service相应方法进行数据保存到数据库中，现只对数据输出
        TDorm dorm = new TDorm();
        for (int i = 0; i < listob.size(); i++) {
            List<Object> lo = listob.get(i);
//          如果这条数据存在则调用修改方法，修改这条数据
            dorm.setTung(String.valueOf(lo.get(0)));
            dorm.setDormNum(String.valueOf(lo.get(1)));
            dorm.setGrade(String.valueOf(lo.get(2)));
            dorm.setCollege(String.valueOf(lo.get(3)));
            dorm.setMajor(String.valueOf(lo.get(4)));
            dorm.setClassName(String.valueOf(lo.get(5)));
            dorm.setStuNum(String.valueOf(lo.get(6)));
            dorm.setName(String.valueOf(lo.get(7)));
            dorm.setSex(String.valueOf(lo.get(8)));
            dorm.setRemarks(String.valueOf(lo.get(9)));
            dormMapper.insert(dorm);
        }
        return new ResponseEntry().ok("okk", "okk");
    }

    @Autowired
    private ICheckDormMapper checkDormMapper;

    public void excel(HttpServletResponse response,String position,String className,String college) throws IOException {
        //确定编码的格式
        response.setCharacterEncoding("UTF-8");
        //获取导出的数据
        QueryWrapper<TCheckDorm> wrapper = new QueryWrapper<>();
        if (position.equals("headmaster"))
            wrapper.eq("class_name",className);
        else
            wrapper.eq("college",college);
        List<TCheckDorm> checkDormList = checkDormMapper.selectList(wrapper);
        //创建excel文件
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建sheet页
        HSSFSheet sheet = wb.createSheet("学生信息表");
        //创建标题行
        HSSFRow titleRow = sheet.createRow(0);
        titleRow.createCell(0).setCellValue("查寝编号");
        titleRow.createCell(1).setCellValue("栋数");
        titleRow.createCell(2).setCellValue("寝室号");
        titleRow.createCell(3).setCellValue("查寝时间");
        titleRow.createCell(4).setCellValue("是否合格");
        titleRow.createCell(5).setCellValue("合格描述");
        titleRow.createCell(6).setCellValue("不合格描述");
        titleRow.createCell(7).setCellValue("反馈描述");
        //遍历将数据放到excel列中
        SimpleDateFormat format1 = new SimpleDateFormat("yyyy-MM-dd");
        for (TCheckDorm checkDorm : checkDormList) {
            HSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
            dataRow.createCell(0).setCellValue(checkDorm.getId());
            dataRow.createCell(1).setCellValue(checkDorm.getTung());
            dataRow.createCell(2).setCellValue(checkDorm.getDormNum());
            String time = format1.format(checkDorm.getCheckTime());
            dataRow.createCell(3).setCellValue(time);
            dataRow.createCell(4).setCellValue(checkDorm.getState());
            dataRow.createCell(5).setCellValue(checkDorm.getQualifiedDescribe());
            dataRow.createCell(6).setCellValue(checkDorm.getUnqualifiedDescribe());
            dataRow.createCell(7).setCellValue(checkDorm.getFeedbackDescribe());
        }
            /*   // 设置下载时客户端Excel的名称
            String filename =new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()) + ".xls";
               response.setContentType("application/vnd.ms-excel");
               response.setHeader("Content-disposition", "attachment;filename=" + filename);  */

        // 设置下载时客户端Excel的名称   （上面注释的改进版本，上面的中文不支持）
        response.setContentType("application/octet-stream;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename="
                //该版本为2003-的版本
                + new String("查寝结果列表".getBytes(), "iso-8859-1")
                + ".xls");
        OutputStream ouputStream = response.getOutputStream();
        wb.write(ouputStream);
        ouputStream.flush();
        ouputStream.close();
    }

    @Autowired
    private IFiveoneMapper fiveoneMapper;

    public void downUnpass(HttpServletResponse response,String position,String className,String college) throws IOException {
        //确定编码的格式
        response.setCharacterEncoding("UTF-8");
        //获取导出的数据
        QueryWrapper<TFiveone> wrapper = new QueryWrapper<>();
        if (position.equals("headmaster"))
            wrapper.eq("class_name",className)
                    .eq("work_address","");
        else
            wrapper.eq("college",college)
                    .eq("work_address","");
        List<TFiveone> fiveoneList = fiveoneMapper.selectList(wrapper);
        //创建excel文件
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建sheet页
        HSSFSheet sheet = wb.createSheet("学生五个一作业未交表");
        //创建标题行
        HSSFRow titleRow = sheet.createRow(0);
        titleRow.createCell(0).setCellValue("编号");
        titleRow.createCell(1).setCellValue("姓名");
        titleRow.createCell(2).setCellValue("学号");
        titleRow.createCell(3).setCellValue("班级");
        titleRow.createCell(4).setCellValue("学院");
        titleRow.createCell(5).setCellValue("班主任");
        titleRow.createCell(6).setCellValue("学期");
        for (TFiveone fiveone : fiveoneList) {
            HSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
            dataRow.createCell(0).setCellValue(fiveone.getId());
            dataRow.createCell(1).setCellValue(fiveone.getName());
            dataRow.createCell(2).setCellValue(fiveone.getStuNum());
            dataRow.createCell(3).setCellValue(fiveone.getClassName());
            dataRow.createCell(4).setCellValue(fiveone.getCollege());
            dataRow.createCell(5).setCellValue(fiveone.getHeadmasterName());
            dataRow.createCell(6).setCellValue(fiveone.getSemester());
        }
            /*   // 设置下载时客户端Excel的名称
            String filename =new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()) + ".xls";
               response.setContentType("application/vnd.ms-excel");
               response.setHeader("Content-disposition", "attachment;filename=" + filename);  */

        // 设置下载时客户端Excel的名称   （上面注释的改进版本，上面的中文不支持）
        response.setContentType("application/octet-stream;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename="
                //该版本为2003-的版本
                + new String("五个一作业未交表".getBytes(), "iso-8859-1")
                + ".xls");
        OutputStream ouputStream = response.getOutputStream();
        wb.write(ouputStream);
        ouputStream.flush();
        ouputStream.close();
    }
}
